package com.qujia.station.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.qujia.common.core.domain.AjaxResult;
import com.qujia.customer.enums.CardRecordType;
import com.qujia.customer.enums.CardSumPayTypeEnum;
import com.qujia.customer.enums.RefundTypeEnum;
import com.qujia.station.enums.PayTypeNameEnum;
import com.qujia.station.service.IClassSettlementService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
public class ClassSettlementExportController {

    @Autowired
    private IClassSettlementService iClassSettlementService;

    @GetMapping("/classSettlementExport/{classNo}")
    public AjaxResult classSettlementExport(@PathVariable("classNo") String classNo, HttpServletResponse response) {
       //查询班结详情
        Map<String, Object> stringObjectMap = iClassSettlementService.classInfoByClassNo(classNo);
        HSSFWorkbook workbook = export(stringObjectMap);
        try {
            response.reset();
            response.setContentType("application/cnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition","attachment;filename=班结记录.xls");
            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
            out.close();

        }catch (Exception e) {
            e.printStackTrace();
        }
        return AjaxResult.success();
    }

    public static HSSFWorkbook export(Map<String, Object> stringObjectMap) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        List<String> title1 = new LinkedList<>();
        List<String> title2 = new LinkedList<>();
        List<String> title3 = new LinkedList<>();
        List<String> title4 = new LinkedList<>();
        List<String> title5 = new LinkedList<>();
        List<String> title6 = new LinkedList<>();
        List<String> title7 = new LinkedList<>();
        List<String> title8 = new LinkedList<>();

        //消费-油品汇总
        title1.add("类别");
        title1.add("单价");
        title1.add("加油数量(L/kg)");
        title1.add("应收(元)");
        title1.add("实收(元)");
        title1.add("立减优惠合计(元)");
        title1.add("优惠券优惠合计(元)");
        title1.add("笔数");

        //消费-支付方式汇总
        title1.add("支付方式");
        title1.add("消费合计(元)");
        title1.add("退款合计(元)");
        title1.add("立减优惠合计(元)");
        title1.add("优惠券优惠合计(元)");
        title1.add("笔数");

        //消费-员工方式汇总
        title8.add("账号");
        title8.add("应收(元)");
        title8.add("实收(元)");
        title8.add("立减优惠合计(元)");
        title8.add("优惠券优惠合计(元)");
        title8.add("退款(元)");
        title8.add("笔数");

        //会员卡-消费汇总
        title2.add("消费类型");
        title2.add("实收(元)");
        title2.add("优惠(元)");
        title2.add("笔数");

        //退款-汇总
        title2.add("退款类型");
        title2.add("金额(元)");
        title2.add("笔数");

        //优惠券使用汇总
        title3.add("优惠券类型");
        title3.add("优惠券优惠金额(元)");
        title3.add("优惠券使用数量(张)");

        //充值-汇总
        title4.add("充值类型");
        title4.add("实收(元)");
        title4.add("赠送(元)");
        title4.add("笔数");

        //充值-员工方式汇总
        title5.add("账号");
        title5.add("应收(元)");
        title5.add("实收(元)");
        title5.add("优惠(元)");
        title5.add("笔数");

        //油枪交接
        title6.add("油枪");
        title6.add("油品名");
        title6.add("油价-单价(元)");
        title6.add("开始泵码");
        title6.add("结束泵码");
        title6.add("走字数");
        title6.add("数量(L/kg)");
        title6.add("油枪");
        title6.add("油品名");
        title6.add("油价-单价(元)");
        title6.add("开始泵码");
        title6.add("结束泵码");
        title6.add("走字数");
        title6.add("数量(L/kg)");

        //班结对账汇总
        title7.add("消费合计应收(元)");
        title7.add("消费合计实收(元)");
        title7.add("消费合计优惠(元)");
        title7.add("消费赠送总金额(元)");
        title7.add("消费合计退款(元)");
        title7.add("优惠券核销金额(元)");
        title7.add("充值合计实收(元)");
        title7.add("充值立减优惠(元)");
        title7.add("充值合计赠送(元)");
        title7.add("充值合计退款(元)");
        title7.add("秒杀订单金额(元)");
        title7.add("聚合支付消费合计(元)");
        title7.add("到账总金额(元)");



        HSSFSheet sheet = workbook.createSheet();

        //表头样式
        Font font = workbook.createFont();
        font.setBold(true); // 加粗
        font.setFontHeightInPoints((short) 7);
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFont(font);
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框

        //数据样式
        Font dataFont = workbook.createFont();
        dataFont.setFontHeightInPoints((short) 7);
        CellStyle borderStyle = workbook.createCellStyle();
        borderStyle.setBorderBottom(BorderStyle.THIN); //下边框
        borderStyle.setBorderLeft(BorderStyle.THIN);//左边框
        borderStyle.setBorderTop(BorderStyle.THIN);//上边框
        borderStyle.setBorderRight(BorderStyle.THIN);//右边框
        borderStyle.setFont(dataFont);

        //第一行数据字体
        Font firstRowFont = workbook.createFont();
        firstRowFont.setFontHeightInPoints((short) 7);
        HSSFCellStyle firstRowStyle = workbook.createCellStyle();
        firstRowStyle.setFont(firstRowFont);


        //创建行数
        List<HSSFRow> list = new LinkedList<>();
        for(int i=0;i<300;i++){
            list.add(sheet.createRow(i));
        }

        //取第一行
        HSSFRow cells = list.get(0);
        //站点名称
        JSONArray querySummaryByClassNo = JSONArray.parseArray(JSON.toJSONString(stringObjectMap.get("querySummaryByClassNo")));
        if(querySummaryByClassNo!=null && querySummaryByClassNo.size()>0){
            JSONObject summaryObject = JSON.parseObject(JSON.toJSONString(querySummaryByClassNo.get(0)));
            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 3);
            sheet.addMergedRegion(cellRangeAddress);
            HSSFCell cell0 = cells.createCell(0);
            cell0.setCellStyle(firstRowStyle);
            cell0.setCellValue("站点名称："+Optional.ofNullable(summaryObject.get("stationName")).orElse(""));
        }else {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 3);
            sheet.addMergedRegion(cellRangeAddress);
            HSSFCell cell0 = cells.createCell(0);
            cell0.setCellStyle(firstRowStyle);
            cell0.setCellValue("站点名称："+"");
        }
        //班结时间
        if(querySummaryByClassNo!=null && querySummaryByClassNo.size()>0){
            JSONObject summaryObject = JSON.parseObject(JSON.toJSONString(querySummaryByClassNo.get(0)));
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date startTime1 = new Date(Long.valueOf(summaryObject.get("classStartTime")+""));
            String startTime = sdf.format(startTime1);
            Date endTime1 = new Date(Long.valueOf(summaryObject.get("classEndTime")+""));
            String endTime = sdf.format(endTime1);
            CellRangeAddress cellRangeAddress1 = new CellRangeAddress(0, 0, 4, 7);
            sheet.addMergedRegion(cellRangeAddress1);
            HSSFCell cell4 = cells.createCell(4);
            cell4.setCellStyle(firstRowStyle);
            cell4.setCellValue("班结时间："+startTime+"至"+endTime);

            //班结人
            CellRangeAddress cellRangeAddress2 = new CellRangeAddress(0, 0, 8, 9);
            sheet.addMergedRegion(cellRangeAddress2);
            HSSFCell cell13 = cells.createCell(8);
            cell13.setCellStyle(firstRowStyle);
            cell13.setCellValue("班结人："+Optional.ofNullable(summaryObject.get("createName")).orElse(""));//这里的值从数据库获取

            //通道费
            CellRangeAddress cellRangeAddress3 = new CellRangeAddress(0, 0, 10, 14);
            sheet.addMergedRegion(cellRangeAddress3);
            HSSFCell cell16 = cells.createCell(10);
            cell16.setCellStyle(firstRowStyle);
            cell16.setCellValue("通道费：银行卡通道费合计"+summaryObject.get("yhkCommission")+"元，扫码支付通道费合计"+summaryObject.get("otherCommission")+"元，总通道费合计"+summaryObject.get("allCommission")+"元");
        }else {
            CellRangeAddress cellRangeAddress1 = new CellRangeAddress(0, 0, 4, 7);
            sheet.addMergedRegion(cellRangeAddress1);
            HSSFCell cell4 = cells.createCell(4);
            cell4.setCellStyle(firstRowStyle);
            cell4.setCellValue("班结时间："+""+"至"+"");

            //班结人
            CellRangeAddress cellRangeAddress2 = new CellRangeAddress(0, 0, 8, 9);
            sheet.addMergedRegion(cellRangeAddress2);
            HSSFCell cell13 = cells.createCell(8);
            cell13.setCellStyle(firstRowStyle);
            cell13.setCellValue("班结人："+"");

            //通道费
            CellRangeAddress cellRangeAddress3 = new CellRangeAddress(0, 0, 10, 14);
            sheet.addMergedRegion(cellRangeAddress3);
            HSSFCell cell16 = cells.createCell(10);
            cell16.setCellStyle(firstRowStyle);
            cell16.setCellValue("通道费：银行卡通道费合计"+0+"元，扫码支付通道费合计"+0+"元，总通道费合计"+0+"元");
        }

        //取第二行
        HSSFRow cells1 = list.get(1);
        CellRangeAddress cellRangeAddress0 = new CellRangeAddress(1, 1, 0, 7);
        sheet.addMergedRegion(cellRangeAddress0);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress0, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress0, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress0, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress0, sheet); // 上边框
        HSSFCell cell10 = cells1.createCell(0);
        cell10.setCellValue("消费-油品汇总");
        cell10.setCellStyle(style);

        CellRangeAddress cellRangeAddress9 = new CellRangeAddress(1, 1, 8, 13);
        sheet.addMergedRegion(cellRangeAddress9);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress9, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress9, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress9, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress9, sheet); // 上边框
        HSSFCell cell7 = cells1.createCell(8);
        cell7.setCellValue("消费-支付方式汇总");
        cell7.setCellStyle(style);

        HSSFRow cells2 = list.get(2);
        for (int i = 0; i < title1.size(); i++) {
            HSSFCell cell = cells2.createCell(i);
            cell.setCellValue(title1.get(i));
            cell.setCellStyle(style);
        }

        //消费-油品汇总
        JSONArray queryGroupByOil = JSONArray.parseArray(JSON.toJSONString(stringObjectMap.get("queryGroupByOil")));
        if(queryGroupByOil!=null && queryGroupByOil.size()>0){
            for (int j = 0; j < queryGroupByOil.size(); j++) {
                HSSFRow cells3 = list.get(3+j);
                JSONObject jsonObject = JSON.parseObject(JSON.toJSONString(queryGroupByOil.get(j)));
                HSSFCell cell30 = cells3.createCell(0);
                cell30.setCellStyle(borderStyle);
                cell30.setCellValue(Optional.ofNullable(jsonObject.get("oil_name")).orElse("")+"");
                HSSFCell cell31 = cells3.createCell(1);
                cell31.setCellStyle(borderStyle);
                cell31.setCellValue(Optional.ofNullable(jsonObject.get("oil_price")).orElse("")+"");
                HSSFCell cell32 = cells3.createCell(2);
                cell32.setCellStyle(borderStyle);
                cell32.setCellValue(Optional.ofNullable(jsonObject.get("order_liters")).orElse("")+"");
                HSSFCell cell33 = cells3.createCell(3);
                cell33.setCellStyle(borderStyle);
                cell33.setCellValue(Optional.ofNullable(jsonObject.get("receivable_amt")).orElse("")+"");
                HSSFCell cell34 = cells3.createCell(4);
                cell34.setCellStyle(borderStyle);
                cell34.setCellValue(Optional.ofNullable(jsonObject.get("amt")).orElse("")+"");
                HSSFCell cell35 = cells3.createCell(5);
                cell35.setCellStyle(borderStyle);
                cell35.setCellValue(Optional.ofNullable(jsonObject.get("discount_amt")).orElse("")+"");
                HSSFCell cell36 = cells3.createCell(6);
                cell36.setCellStyle(borderStyle);
                cell36.setCellValue(Optional.ofNullable(jsonObject.get("discount_coupon_amt")).orElse("")+"");
                HSSFCell cell37 = cells3.createCell(7);
                cell37.setCellStyle(borderStyle);
                cell37.setCellValue(Optional.ofNullable(jsonObject.get("order_count")).orElse("")+"");
            }
        }

        //消费-支付方式汇总
        JSONArray queryGroupByPayType = JSONArray.parseArray(JSON.toJSONString(stringObjectMap.get("queryGroupByPayType")));
        if(queryGroupByPayType!=null && queryGroupByPayType.size()>0){
            for (int j = 0; j < queryGroupByPayType.size(); j++) {
                JSONObject jsonObject = JSON.parseObject(JSON.toJSONString(queryGroupByPayType.get(j)));
                HSSFRow cells3 = list.get(3+j);
                HSSFCell cell30 = cells3.createCell(8);
                cell30.setCellStyle(borderStyle);
                cell30.setCellValue(Optional.ofNullable(PayTypeNameEnum.getPayTypeName(jsonObject.get("pay_type")+"")).orElse("")+"");
                HSSFCell cell31 = cells3.createCell(9);
                cell31.setCellStyle(borderStyle);
                cell31.setCellValue(Optional.ofNullable(jsonObject.get("amt")).orElse("")+"");
                HSSFCell cell32 = cells3.createCell(10);
                cell32.setCellStyle(borderStyle);
                cell32.setCellValue(Optional.ofNullable(jsonObject.get("refund_amt")).orElse("")+"");
                HSSFCell cell33 = cells3.createCell(11);
                cell33.setCellStyle(borderStyle);
                cell33.setCellValue(Optional.ofNullable(jsonObject.get("discount_amt")).orElse("")+"");
                HSSFCell cell34 = cells3.createCell(12);
                cell34.setCellStyle(borderStyle);
                cell34.setCellValue(Optional.ofNullable(jsonObject.get("discount_coupon_amt")).orElse("")+"");
                HSSFCell cell35 = cells3.createCell(13);
                cell35.setCellStyle(borderStyle);
                cell35.setCellValue(Optional.ofNullable(jsonObject.get("order_count")).orElse("")+"");
            }
        }

        //会员卡-消费汇总
        int i1 = queryGroupByOil.size() + 3;
        HSSFRow cells4 = list.get(i1);
        CellRangeAddress cellRangeAddress115 = new CellRangeAddress(i1, i1, 0, 3);
        sheet.addMergedRegion(cellRangeAddress115);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress115, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress115, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress115, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress115, sheet); // 上边框
        HSSFCell cell115 = cells4.createCell(0);
        cell115.setCellValue("会员卡-消费汇总");
        cell115.setCellStyle(style);

        CellRangeAddress cellRangeAddress4 = new CellRangeAddress(i1, i1, 4, 6);
        sheet.addMergedRegion(cellRangeAddress4);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress4, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress4, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress4, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress4, sheet); // 上边框
        HSSFCell cell1 = cells4.createCell(4);
        cell1.setCellValue("退款-汇总");
        cell1.setCellStyle(style);

        HSSFRow cells5 = list.get(i1+1);
        for (int i = 0; i < title2.size(); i++) {
            HSSFCell cell = cells5.createCell(i);
            cell.setCellValue(title2.get(i));
            cell.setCellStyle(style);
        }

        //会员卡-消费汇总
        int i2 = i1 + 2;
        JSONArray queryGroupByRecharge = JSONArray.parseArray(JSON.toJSONString(stringObjectMap.get("queryGroupByRecharge")));
        if(queryGroupByRecharge!=null && queryGroupByRecharge.size()>0){
            for (int j = 0; j < queryGroupByRecharge.size(); j++) {
                HSSFRow cells3 = list.get(i2+j);
                JSONObject jsonObject = JSON.parseObject(JSON.toJSONString(queryGroupByRecharge.get(j)));
                HSSFCell cell60 = cells3.createCell(0);
                cell60.setCellStyle(borderStyle);
                cell60.setCellValue(Optional.ofNullable(CardRecordType.getPayTypeName(jsonObject.get("payType")+"")).orElse("")+"");
                HSSFCell cell61 = cells3.createCell(1);
                cell61.setCellStyle(borderStyle);
                cell61.setCellValue(Optional.ofNullable(jsonObject.get("amt")).orElse("")+"");
                HSSFCell cell62 = cells3.createCell(2);
                cell62.setCellStyle(borderStyle);
                cell62.setCellValue(Optional.ofNullable(jsonObject.get("giveAmt")).orElse("")+"");
                HSSFCell cell63 = cells3.createCell(3);
                cell63.setCellStyle(borderStyle);
                cell63.setCellValue(Optional.ofNullable(jsonObject.get("orderCount")).orElse("")+"");
            }
        }

        //退款-汇总
        JSONArray refundSum = JSONArray.parseArray(JSON.toJSONString(stringObjectMap.get("refundSummary")));
        if(refundSum!=null && refundSum.size()>0){
            for (int j = 0; j < refundSum.size(); j++) {
                //第n行开始
                HSSFRow cells3 = list.get(i2 + j);
                JSONObject jsonObject = JSON.parseObject(JSON.toJSONString(refundSum.get(j)));
                HSSFCell cell30 = cells3.createCell(4);
                cell30.setCellStyle(borderStyle);
                cell30.setCellValue(Optional.ofNullable(RefundTypeEnum.getRefundTypeName(jsonObject.get("payType") + "")).orElse("")+"");
                HSSFCell cell31 = cells3.createCell(5);
                cell31.setCellStyle(borderStyle);
                cell31.setCellValue(Optional.ofNullable(jsonObject.get("refundAmt")).orElse("")+"");
                HSSFCell cell32 = cells3.createCell(6);
                cell32.setCellStyle(borderStyle);
                cell32.setCellValue(Optional.ofNullable(jsonObject.get("orderCount")).orElse("")+"");
            }
        }

        //优惠券使用汇总
        int i3 = queryGroupByOil.size()+queryGroupByRecharge.size()+5;
        HSSFRow cells3 = list.get(i3);
        CellRangeAddress cellRangeAddress5 = new CellRangeAddress(i3, i3, 0, 2);
        sheet.addMergedRegion(cellRangeAddress5);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress5, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress5, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress5, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress5, sheet); // 上边框
        HSSFCell cell = cells3.createCell(0);
        cell.setCellValue("优惠券使用汇总");
        cell.setCellStyle(style);

        HSSFRow cells6 = list.get(i3 + 1);
        for (int i = 0; i < title3.size(); i++) {
            HSSFCell cell2 = cells6.createCell(i);
            cell2.setCellValue(title3.get(i));
            cell2.setCellStyle(style);
        }

        int i4 = i3 + 2;
        JSONArray couponSummary = JSONArray.parseArray(JSON.toJSONString(stringObjectMap.get("couponSummary")));
        if(couponSummary!=null && couponSummary.size()>0){
            for (int j = 0; j < couponSummary.size(); j++) {
                HSSFRow cells7 = list.get(i4 + j);
                JSONObject jsonObject = JSON.parseObject(JSON.toJSONString(couponSummary.get(j)));
                HSSFCell cell30 = cells7.createCell(0);
                cell30.setCellStyle(borderStyle);
                cell30.setCellValue(Optional.ofNullable(jsonObject.get("couponType")).orElse("")+"");
                HSSFCell cell31 = cells7.createCell(1);
                cell31.setCellStyle(borderStyle);
                cell31.setCellValue(Optional.ofNullable(jsonObject.get("couponAmt")).orElse("")+"");
                HSSFCell cell32 = cells7.createCell(2);
                cell32.setCellStyle(borderStyle);
                cell32.setCellValue(Optional.ofNullable(jsonObject.get("count")).orElse("")+"");
            }
        }

        //充值-汇总
        int i5 = queryGroupByPayType.size()+3;
        HSSFRow cells7 = list.get(i5);
        CellRangeAddress cellRangeAddress6 = new CellRangeAddress(i5, i5, 8, 13);
        sheet.addMergedRegion(cellRangeAddress6);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress6, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress6, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress6, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress6, sheet); // 上边框
        HSSFCell cell2 = cells7.createCell(8);
        cell2.setCellValue("充值-汇总");
        cell2.setCellStyle(style);

        //充值-汇总
        HSSFRow cells8 = list.get(i5 + 1);
        for (int i = 0; i < title4.size(); i++) {
            HSSFCell cell3 = cells8.createCell(8+i);
            cell3.setCellValue(title4.get(i));
            cell3.setCellStyle(style);
        }

        //充值-汇总
        int i6 = i5 + 2;
        JSONArray transactionSummary = JSONArray.parseArray(JSON.toJSONString(stringObjectMap.get("transactionSummary")));
        if(transactionSummary!=null && transactionSummary.size()>0){
            for (int j = 0; j < transactionSummary.size(); j++) {
                HSSFRow cells9 = list.get(i6 + j);
                JSONObject jsonObject = JSON.parseObject(JSON.toJSONString(transactionSummary.get(j)));
                HSSFCell cell30 = cells9.createCell(8);
                cell30.setCellStyle(borderStyle);
                cell30.setCellValue(Optional.ofNullable(CardSumPayTypeEnum.getCardSumPayTypeName(jsonObject.get("payType") + "")).orElse("0")+"");
                HSSFCell cell31 = cells9.createCell(9);
                cell31.setCellStyle(borderStyle);
                cell31.setCellValue(Optional.ofNullable(jsonObject.get("amt")).orElse("0")+"");
                HSSFCell cell32 = cells9.createCell(10);
                cell32.setCellStyle(borderStyle);
                cell32.setCellValue(Optional.ofNullable(jsonObject.get("giveAmt")).orElse("0")+"");
                HSSFCell cell33 = cells9.createCell(11);
                cell33.setCellStyle(borderStyle);
                cell33.setCellValue(Optional.ofNullable(jsonObject.get("orderCount")).orElse("0")+"");
            }
        }


        int i12 = queryGroupByOil.size() + queryGroupByRecharge.size()+couponSummary.size()+ 7;
        HSSFRow cells23 = list.get(i12);
        CellRangeAddress cellRangeAddress10 = new CellRangeAddress(i12, i12, 0, 6);
        sheet.addMergedRegion(cellRangeAddress10);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress10, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress10, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress10, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress10, sheet); // 上边框
        HSSFCell cell8 = cells23.createCell(0);
        cell8.setCellValue("消费-员工方式汇总");
        cell8.setCellStyle(style);

        HSSFRow cells22 = list.get(i12 + 1);
        for (int i = 0; i < title8.size(); i++) {
            HSSFCell cell3 = cells22.createCell(i);
            cell3.setCellValue(title8.get(i));
            cell3.setCellStyle(style);
        }

        //消费-员工方式汇总
        int i13 = i12 + 2;
        JSONArray queryGroupByEmployees = JSONArray.parseArray(JSON.toJSONString(stringObjectMap.get("listDetail4User")));
        if(queryGroupByEmployees!=null && queryGroupByEmployees.size()>0){
            for (int j = 0; j < queryGroupByEmployees.size(); j++) {
                JSONObject jsonObject = JSON.parseObject(JSON.toJSONString(queryGroupByEmployees.get(j)));
                HSSFRow cells9 = list.get(i13 + j);
                HSSFCell cell30 = cells9.createCell(0);
                cell30.setCellStyle(borderStyle);
                cell30.setCellValue(Optional.ofNullable(jsonObject.get("oil_personnel_name")).orElse("")+"");
                HSSFCell cell31 = cells9.createCell(1);
                cell31.setCellStyle(borderStyle);
                cell31.setCellValue(Optional.ofNullable(jsonObject.get("receivable_amt")).orElse("")+"");
                HSSFCell cell32 = cells9.createCell(2);
                cell32.setCellStyle(borderStyle);
                cell32.setCellValue(Optional.ofNullable(jsonObject.get("amt")).orElse("")+"");
                HSSFCell cell33 = cells9.createCell(3);
                cell33.setCellStyle(borderStyle);
                cell33.setCellValue(Optional.ofNullable(jsonObject.get("discount_amt")).orElse("")+"");
                HSSFCell cell34 = cells9.createCell(4);
                cell34.setCellStyle(borderStyle);
                cell34.setCellValue(Optional.ofNullable(jsonObject.get("discount_coupon_amt")).orElse("")+"");
                HSSFCell cell35 = cells9.createCell(5);
                cell35.setCellStyle(borderStyle);
                cell35.setCellValue(Optional.ofNullable(jsonObject.get("refund_amt")).orElse("")+"");
                HSSFCell cell36 = cells9.createCell(6);
                cell36.setCellStyle(borderStyle);
                cell36.setCellValue(Optional.ofNullable(jsonObject.get("order_count")).orElse("")+"");
            }
        }

        //充值-员工方式汇总
        int i7 = queryGroupByOil.size()+queryGroupByRecharge.size()+queryGroupByEmployees.size()+couponSummary.size()+9;
        HSSFRow cells11 = list.get(i7);
        CellRangeAddress cellRangeAddress7 = new CellRangeAddress(i7, i7, 0, 4);
        sheet.addMergedRegion(cellRangeAddress7);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress7, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress7, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress7, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress7, sheet); // 上边框
        HSSFCell cell5 = cells11.createCell(0);
        cell5.setCellValue("充值-员工方式汇总");
        cell5.setCellStyle(style);

        //充值-员工方式汇总
        HSSFRow cells10 = list.get(i7 + 1);
        for (int i = 0; i < title5.size(); i++) {
            HSSFCell cell3 = cells10.createCell(i);
            cell3.setCellValue(title5.get(i));
            cell3.setCellStyle(style);
        }

        //充值-员工方式汇总
        int i8 = i7 + 2;
        JSONArray changeClassSettlement4User = JSONArray.parseArray(JSON.toJSONString(stringObjectMap.get("changeClassSettlement4User")));
        if(changeClassSettlement4User!=null && changeClassSettlement4User.size()>0){
            for (int j = 0; j < changeClassSettlement4User.size(); j++) {
                HSSFRow cells9 = list.get(i8 + j);
                JSONObject jsonObject = JSON.parseObject(JSON.toJSONString(changeClassSettlement4User.get(j)));
                HSSFCell cell30 = cells9.createCell(0);
                cell30.setCellStyle(borderStyle);
                cell30.setCellValue(Optional.ofNullable(jsonObject.get("oilPersonnelName")).orElse("")+"");
                HSSFCell cell31 = cells9.createCell(1);
                cell31.setCellStyle(borderStyle);
                cell31.setCellValue(Optional.ofNullable(jsonObject.get("amt")).orElse("")+"");
                HSSFCell cell32 = cells9.createCell(2);
                cell32.setCellStyle(borderStyle);
                cell32.setCellValue(Optional.ofNullable(jsonObject.get("amt")).orElse("")+"");
                HSSFCell cell33 = cells9.createCell(3);
                cell33.setCellStyle(borderStyle);
                cell33.setCellValue(0);
                HSSFCell cell34 = cells9.createCell(4);
                cell34.setCellStyle(borderStyle);
                cell34.setCellValue(Optional.ofNullable(jsonObject.get("orderCount")).orElse("")+"");
            }
        }

        //比较四个列表大小取最大行数
//        JSONArray maxJsonArray = Arrays.asList(changeClassSettlement4User, refundSum, transactionSummary).stream()
//                .max(Comparator.comparingInt(List::size))
//                .orElse(null);

        //油枪交接
        int i9 = queryGroupByOil.size()+queryGroupByRecharge.size()+queryGroupByEmployees.size()+couponSummary.size()+12;
        HSSFRow cells9 = list.get(i9);
        CellRangeAddress cellRangeAddress8 = new CellRangeAddress(i9, i9, 0, 13);
        sheet.addMergedRegion(cellRangeAddress8);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress8, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress8, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress8, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress8, sheet); // 上边框
        HSSFCell cell3 = cells9.createCell(0);
        cell3.setCellValue("油枪交接");
        cell3.setCellStyle(style);

        HSSFRow cells12 = list.get(i9 + 1);
        for (int i = 0; i < title6.size(); i++) {
            HSSFCell cell6 = cells12.createCell(i);
            cell6.setCellValue(title6.get(i));
            cell6.setCellStyle(style);
        }

        int i10 = i9 + 2;
        JSONArray queryGroupByOilGun = JSONArray.parseArray(JSON.toJSONString(stringObjectMap.get("queryGroupByOilGun")));
        //最终行数
        int i = 0 + i10;
        if(queryGroupByOilGun!=null && queryGroupByOilGun.size()>0){
            for (int j = 0; j < queryGroupByOilGun.size(); j+=2) {
                HSSFRow cells13 = list.get(i++);
                List<Object> subList = queryGroupByOilGun.subList(j, Math.min(j + 2, queryGroupByOilGun.size()));

                if(subList.size()==1){
                    if(subList.get(0)!=null){
                        JSONObject jsonObject = JSON.parseObject(JSON.toJSONString(subList.get(0)));
                        HSSFCell cell30 = cells13.createCell(0);
                        cell30.setCellStyle(borderStyle);
                        cell30.setCellValue(Optional.ofNullable(jsonObject.get("oil_gun")).orElse("")+"");
                        HSSFCell cell31 = cells13.createCell(1);
                        cell31.setCellStyle(borderStyle);
                        cell31.setCellValue(Optional.ofNullable(jsonObject.get("oil_name")).orElse("")+"");
                        HSSFCell cell32 = cells13.createCell(2);
                        cell32.setCellStyle(borderStyle);
                        cell32.setCellValue(Optional.ofNullable(jsonObject.get("oil_price")).orElse("")+"");
                        HSSFCell cell33 = cells13.createCell(3);
                        cell33.setCellStyle(borderStyle);
                        cell33.setCellValue(Optional.ofNullable(jsonObject.get("startFlowMeter")).orElse("")+"");
                        HSSFCell cell34 = cells13.createCell(4);
                        cell34.setCellStyle(borderStyle);
                        cell34.setCellValue(Optional.ofNullable(jsonObject.get("endFlowMeter")).orElse("")+"");
                        HSSFCell cell35 = cells13.createCell(5);
                        cell35.setCellStyle(borderStyle);
                        cell35.setCellValue(Optional.ofNullable(jsonObject.get("totalWordCount")).orElse("")+"");
                        HSSFCell cell36 = cells13.createCell(6);
                        cell36.setCellStyle(borderStyle);
                        cell36.setCellValue(Optional.ofNullable(jsonObject.get("order_liters")).orElse("")+"");

                        HSSFCell cell37 = cells13.createCell(7);
                        cell37.setCellStyle(borderStyle);
                        HSSFCell cell38 = cells13.createCell(8);
                        cell38.setCellStyle(borderStyle);
                        HSSFCell cell39 = cells13.createCell(9);
                        cell39.setCellStyle(borderStyle);
                        HSSFCell cell40 = cells13.createCell(10);
                        cell40.setCellStyle(borderStyle);
                        HSSFCell cell41 = cells13.createCell(11);
                        cell41.setCellStyle(borderStyle);
                        HSSFCell cell42 = cells13.createCell(12);
                        cell42.setCellStyle(borderStyle);
                        HSSFCell cell43 = cells13.createCell(13);
                        cell43.setCellStyle(borderStyle);
                    }
                }else if (subList.size()==2){
                    if(subList.get(0)!=null){
                        JSONObject jsonObject = JSON.parseObject(JSON.toJSONString(subList.get(0)));
                        HSSFCell cell30 = cells13.createCell(0);
                        cell30.setCellStyle(borderStyle);
                        cell30.setCellValue(Optional.ofNullable(jsonObject.get("oil_gun")).orElse("")+"");
                        HSSFCell cell31 = cells13.createCell(1);
                        cell31.setCellStyle(borderStyle);
                        cell31.setCellValue(Optional.ofNullable(jsonObject.get("oil_name")).orElse("")+"");
                        HSSFCell cell32 = cells13.createCell(2);
                        cell32.setCellStyle(borderStyle);
                        cell32.setCellValue(Optional.ofNullable(jsonObject.get("oil_price")).orElse("")+"");
                        HSSFCell cell33 = cells13.createCell(3);
                        cell33.setCellStyle(borderStyle);
                        cell33.setCellValue(Optional.ofNullable(jsonObject.get("startFlowMeter")).orElse("")+"");
                        HSSFCell cell34 = cells13.createCell(4);
                        cell34.setCellStyle(borderStyle);
                        cell34.setCellValue(Optional.ofNullable(jsonObject.get("endFlowMeter")).orElse("")+"");
                        HSSFCell cell35 = cells13.createCell(5);
                        cell35.setCellStyle(borderStyle);
                        cell35.setCellValue(Optional.ofNullable(jsonObject.get("totalWordCount")).orElse("")+"");
                        HSSFCell cell36 = cells13.createCell(6);
                        cell36.setCellStyle(borderStyle);
                        cell36.setCellValue(Optional.ofNullable(jsonObject.get("order_liters")).orElse("")+"");
                    }

                    if(subList.get(1)!=null){
                        JSONObject jsonObject1 = JSON.parseObject(JSON.toJSONString(subList.get(1)));
                        HSSFCell cell37 = cells13.createCell(7);
                        cell37.setCellStyle(borderStyle);
                        cell37.setCellValue(Optional.ofNullable(jsonObject1.get("oil_gun")).orElse("")+"");
                        HSSFCell cell38 = cells13.createCell(8);
                        cell38.setCellStyle(borderStyle);
                        cell38.setCellValue(Optional.ofNullable(jsonObject1.get("oil_name")).orElse("")+"");
                        HSSFCell cell39 = cells13.createCell(9);
                        cell39.setCellStyle(borderStyle);
                        cell39.setCellValue(Optional.ofNullable(jsonObject1.get("oil_price")).orElse("")+"");
                        HSSFCell cell40 = cells13.createCell(10);
                        cell40.setCellStyle(borderStyle);
                        cell40.setCellValue(Optional.ofNullable(jsonObject1.get("startFlowMeter")).orElse("")+"");
                        HSSFCell cell41 = cells13.createCell(11);
                        cell41.setCellStyle(borderStyle);
                        cell41.setCellValue(Optional.ofNullable(jsonObject1.get("endFlowMeter")).orElse("")+"");
                        HSSFCell cell42 = cells13.createCell(12);
                        cell42.setCellStyle(borderStyle);
                        cell42.setCellValue(Optional.ofNullable(jsonObject1.get("totalWordCount")).orElse("")+"");
                        HSSFCell cell43 = cells13.createCell(13);
                        cell43.setCellStyle(borderStyle);
                        cell43.setCellValue(Optional.ofNullable(jsonObject1.get("order_liters")).orElse("")+"");
                    }
                }
            }
        }
        //计算走字数合计
        BigDecimal totalWordCount = BigDecimal.ZERO;
        if(queryGroupByOilGun!=null && queryGroupByOilGun.size()>0){
            for (int j = 0; j < queryGroupByOilGun.size(); j++) {
                JSONObject jsonObject = JSON.parseObject(JSON.toJSONString(queryGroupByOilGun.get(j)));
                if(jsonObject.get("totalWordCount")!=null){
                    totalWordCount = totalWordCount.add(new BigDecimal(jsonObject.get("totalWordCount")+""));
                }
            }
        }
        //走字数合计
        HSSFRow cells13 = list.get(i);
        CellRangeAddress cellRangeAddress11 = new CellRangeAddress(i, i, 1, 13);
        sheet.addMergedRegion(cellRangeAddress11);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress11, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress11, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress11, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress11, sheet); // 上边框
        HSSFCell cell6 = cells13.createCell(0);
        cell6.setCellValue("走字数合计");
        cell6.setCellStyle(style);
        HSSFCell cell36 = cells13.createCell(1);
        cell36.setCellValue(totalWordCount+"");
        cell36.setCellStyle(style);

        //班结对账汇总
        HSSFRow cells14 = list.get(i+1);
        CellRangeAddress cellRangeAddress12 = new CellRangeAddress(i+1, i+1, 0, 3);
        sheet.addMergedRegion(cellRangeAddress12);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress12, sheet); // 下边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress12, sheet); // 左边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress12, sheet); // 有边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress12, sheet); // 上边框
        HSSFCell cell9 = cells14.createCell(0);
        cell9.setCellValue("班结对账汇总");
        cell9.setCellStyle(style);


        int i11 = i + 2;

        //列数下标
        int k0 = 0;
        int k1 = 1;
        int k2 = 2;
        int k3 = 3;
        HSSFRow cells15 = list.get(i11++);
        HSSFCell cell4 = cells15.createCell(k0);
        cell4.setCellValue(title7.get(0));
        cell4.setCellStyle(style);

        HSSFCell cell11 = cells15.createCell(k2);
        cell11.setCellValue(title7.get(1));
        cell11.setCellStyle(style);

        HSSFRow cells16 = list.get(i11++);
        HSSFCell cell12 = cells16.createCell(k0);
        cell12.setCellValue(title7.get(2));
        cell12.setCellStyle(style);

        HSSFCell cell13 = cells16.createCell(k2);
        cell13.setCellValue(title7.get(3));
        cell13.setCellStyle(style);

        HSSFRow cells17 = list.get(i11++);
        HSSFCell cell14 = cells17.createCell(k0);
        cell14.setCellValue(title7.get(4));
        cell14.setCellStyle(style);

        HSSFCell cell15 = cells17.createCell(k2);
        cell15.setCellValue(title7.get(5));
        cell15.setCellStyle(style);

        HSSFRow cells18 = list.get(i11++);
        HSSFCell cell16 = cells18.createCell(k0);
        cell16.setCellValue(title7.get(6));
        cell16.setCellStyle(style);

        HSSFCell cell17 = cells18.createCell(k2);
        cell17.setCellValue(title7.get(7));
        cell17.setCellStyle(style);

        HSSFRow cells19 = list.get(i11++);
        HSSFCell cell18 = cells19.createCell(k0);
        cell18.setCellValue(title7.get(8));
        cell18.setCellStyle(style);

        HSSFCell cell19 = cells19.createCell(k2);
        cell19.setCellValue(title7.get(9));
        cell19.setCellStyle(style);

        HSSFRow cells20 = list.get(i11++);
        HSSFCell cell20 = cells20.createCell(k0);
        cell20.setCellValue(title7.get(10));
        cell20.setCellStyle(style);

        HSSFCell cell21 = cells20.createCell(k2);
        cell21.setCellValue(title7.get(11));
        cell21.setCellStyle(style);

        HSSFRow cells21 = list.get(i11++);
        HSSFCell cell22 = cells21.createCell(k0);
        cell22.setCellValue(title7.get(12));
        cell22.setCellStyle(style);

        if(querySummaryByClassNo!=null && querySummaryByClassNo.size()>0){
            JSONObject summaryObject = JSON.parseObject(JSON.toJSONString(querySummaryByClassNo.get(0)));
            HSSFCell cell23 = cells15.createCell(k1);
            cell23.setCellValue(Optional.ofNullable(summaryObject.get("receivableAmt")).orElse("")+"");
            cell23.setCellStyle(borderStyle);

            HSSFCell cell24 = cells15.createCell(k3);
            cell24.setCellValue(Optional.ofNullable(summaryObject.get("amt")).orElse("")+"");
            cell24.setCellStyle(borderStyle);


            HSSFCell cell25 = cells16.createCell(k1);
            cell25.setCellValue(Optional.ofNullable(summaryObject.get("totalDiscountAmt")).orElse("")+"");
            cell25.setCellStyle(borderStyle);


            HSSFCell cell26 = cells16.createCell(k3);
            cell26.setCellValue(Optional.ofNullable(summaryObject.get("presentAmount")).orElse("")+"");
            cell26.setCellStyle(borderStyle);

            HSSFCell cell27 = cells17.createCell(k1);
            cell27.setCellValue(Optional.ofNullable(summaryObject.get("consumptionRefundAmt")).orElse("")+"");
            cell27.setCellStyle(borderStyle);

            HSSFCell cell28 = cells17.createCell(k3);
            cell28.setCellValue(Optional.ofNullable(summaryObject.get("killCouponAmt")).orElse("")+"");
            cell28.setCellStyle(borderStyle);

            HSSFCell cell29 = cells18.createCell(k1);
            cell29.setCellValue(Optional.ofNullable(summaryObject.get("actualRechargeReceivedAmt")).orElse(BigDecimal.valueOf(0))+"");
            cell29.setCellStyle(borderStyle);

            HSSFCell cell30 = cells18.createCell(k3);
            cell30.setCellValue(Optional.ofNullable(summaryObject.get("rechargeReduceDiscounts")).orElse("")+"");
            cell30.setCellStyle(borderStyle);

            HSSFCell cell31 = cells19.createCell(k1);
            cell31.setCellValue(Optional.ofNullable(summaryObject.get("actualRechargeGiftAmt")).orElse(BigDecimal.valueOf(0))+"");
            cell31.setCellStyle(borderStyle);

            HSSFCell cell32 = cells19.createCell(k3);
            cell32.setCellValue(Optional.ofNullable(summaryObject.get("totalRechargeRefundAmt")).orElse("")+"");
            cell32.setCellStyle(borderStyle);

            HSSFCell cell33 = cells20.createCell(k1);
            cell33.setCellValue(Optional.ofNullable(summaryObject.get("saleActivityAmt")).orElse("")+"");
            cell33.setCellStyle(borderStyle);

            HSSFCell cell34 = cells20.createCell(k3);
            String aggregatedPayment = "";
            if(summaryObject.get("aggregatedPayment")!=null){
                aggregatedPayment = String.valueOf(new BigDecimal(summaryObject.get("aggregatedPayment")+"").stripTrailingZeros());
            }else {
                aggregatedPayment = String.valueOf(BigDecimal.valueOf(0));
            }
            cell34.setCellValue(aggregatedPayment);
            cell34.setCellStyle(borderStyle);

            HSSFCell cell35 = cells21.createCell(k1);
            String totalWithdrawalAmount = "";
            if(summaryObject.get("totalWithdrawalAmount")!=null){
                totalWithdrawalAmount = String.valueOf(new BigDecimal(summaryObject.get("totalWithdrawalAmount")+"").stripTrailingZeros());
            }else {
                totalWithdrawalAmount = String.valueOf(BigDecimal.valueOf(0));
            }
            cell35.setCellValue(totalWithdrawalAmount);
            cell35.setCellStyle(borderStyle);
        }
        return workbook;

    }
}
